﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for Product
/// </summary>
public class DAL_News : clsConnection
{
    DataTable dt = new DataTable();
    clsQueryList clsquery = new clsQueryList();
    public DataTable DAL_News_getByTop(int top)
    {
        try
        {
            dt = ExcuteQuery("SELECT TOP " + top + " * FROM News WHERE status = 1 Order By Date desc");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getData()
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News WHERE Status = 1 ORDER BY Date desc");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getDataByTopAndType(int top, int type)
    {
        try
        {
            dt = ExcuteQuery("SELECT TOP " + top + " * FROM News WHERE Status = 1 AND Type = " + type + " ORDER BY Date desc");
        }
        catch
        {
        }
        return dt;
    }

    public DataTable DAL_News_getDataByType(int type)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News WHERE status = 1 AND Type = " + type + " ORDER BY id DESC");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getDataById(int id)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News WHERE status = 1 AND id = " + id);
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getByWher(string where)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News " + where);
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getByCgid(int Cgid)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News WHERE Status = 1 AND type = " + Cgid + " ORDER BY Date desc");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getAllByCgid(int Cgid)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News WHERE type = " + Cgid + " ORDER BY Date desc");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getByTopCgid(int top, int Cgid, int pid)
    {
        try
        {
            dt = ExcuteQuery("SELECT TOP "+ top +" * FROM News WHERE type = " + Cgid + " AND id <> "+ pid +" ORDER BY Date desc");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getByMkid(int Mkid)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News WHERE Status = 1 AND Maker_Id = " + Mkid + " ORDER BY Date desc");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getByPid(int Pid)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News WHERE Status = 1 AND id = " + Pid);
        }
        catch
        {
        }
        return dt;
    }

    public DataTable DAL_News_getPriceNew(int top)
    {
        try
        {
            dt = ExcuteQuery("SELECT TOP " + top + " * FROM News WHERE status = 1 ORDER BY Date DESC");
        }
        catch
        {
        }
        return dt;
    }
    public DataTable DAL_News_getAllByPid(int Pid)
    {
        try
        {
            dt = ExcuteQuery("SELECT * FROM News WHERE id = " + Pid);
        }
        catch
        {
        }
        return dt;
    }
    //public DataTable DAL_News_getMaxOrder()
    //{
    //    try
    //    {
    //        dt = ExcuteQuery("select max([order]) as 'order' from product");
    //    }
    //    catch
    //    {
    //    }
    //    return dt;
    //}
    public DataTable DAL_News_getMaxPid()
    {
        try
        {
            dt = ExcuteQuery("select max(id) as 'id' from News");
        }
        catch
        {
        }
        return dt;
    }
    public DTO_News DAL_LoadNews(int pid)
    {
        DTO_News dtoP = new DTO_News();
        try
        {
            string sql = "SELECT * FROM News WHERE status = 1 AND id = @pid";
            AddParameter(new SqlParameter("@id", pid));
            dt = ExcuteQuery(sql);
            if (dt.Rows.Count > 0)
            {
                dtoP.ID = (int)dt.Rows[0]["id"];
                dtoP.Title = dt.Rows[0]["title"].ToString();
                dtoP.Contents = dt.Rows[0]["contents"].ToString();
                //dtoP.Price = (float)dt.Rows[0]["Price"];
                dtoP.Image = dt.Rows[0]["image"].ToString();
                //dtoP.ImageLarge = dt.Rows[0]["ImageLarge"].ToString();
                //dtoP.PriceNew = (float)dt.Rows[0]["PriceNew"];
                dtoP.Date = dt.Rows[0]["Date"].ToString();
                //dtoP.Order = (int)dt.Rows[0]["Order"];
                dtoP.Status = (int)dt.Rows[0]["status"];
                //dtoP.Maker_Id = (int)dt.Rows[0]["Maker_Id"];
                dtoP.Type = (int)dt.Rows[0]["type"];
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return dtoP;
    }
    public Boolean DAL_News_Update(DTO_News obj)
    {
        try
        {
            string sql = "UPDATE News SET Title = @title, Contents = @contents, Image = @image, Status = @status, Type = @type WHERE id = @id";
            //AddParameter(new SqlParameter("@name", obj.Name));
            AddParameter(new SqlParameter("@title", obj.Title));
            AddParameter(new SqlParameter("@contents", obj.Contents));
            AddParameter(new SqlParameter("@image", obj.Image));
            AddParameter(new SqlParameter("@type", obj.Type));
            AddParameter(new SqlParameter("@status", obj.Status));
            //AddParameter(new SqlParameter("@order", obj.Order));
            //AddParameter(new SqlParameter("@status", obj.Status));
            //AddParameter(new SqlParameter("@maker_id", obj.Maker_Id));
            //AddParameter(new SqlParameter("@category_id", obj.Category_Id));
            AddParameter(new SqlParameter("@id", obj.ID));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch
        {
            return false;
        }
    }
    public Boolean DAL_News_Insert(DTO_News obj)
    {
        try
        {
            string sql = "INSERT INTO News(Title, Contents, Date, Image, Type, Status) VALUES (@title, @contents, @date, @image, @type, @status)";
            AddParameter(new SqlParameter("@title", obj.Title));
            AddParameter(new SqlParameter("@contents", obj.Contents));
            AddParameter(new SqlParameter("@date", obj.Date));
            AddParameter(new SqlParameter("@image", obj.Image));
            AddParameter(new SqlParameter("@type", obj.Type));
            AddParameter(new SqlParameter("@status", obj.Status));
            //AddParameter(new SqlParameter("@date", obj.Date));
            //AddParameter(new SqlParameter("@order", obj.Order));
            //AddParameter(new SqlParameter("@status", obj.Status));
            //AddParameter(new SqlParameter("@maker_id", obj.Maker_Id));
            //AddParameter(new SqlParameter("@category_id", obj.Category_Id));

            if (ExcuteNonQuery(sql))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }
    public Boolean DAL_News_addtodel(int id)
    {
        try
        {
            string sql = "delete from News where id=" + id;
            clsquery.addSQL(sql);
        }
        catch
        {
            return false;
        }
        return true;
    }
    public Boolean DAL_News_del()
    {
        try
        {
            clsquery.Execute();
        }
        catch
        {
            return false;
        }
        return true;
    }

    public DataTable DAL_News_Search(string keyword)
    {
        try
        {
            string sql = "SELECT * FROM News WHERE status = 1 AND title LIKE N'%" + keyword + "%' ORDER BY Date DESC";
            dt = ExcuteQuery(sql);
        }
        catch
        {
            
        }
        return dt;
    }
}